/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Manager;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author wienda
 */
public class LaporanController {

    TestConnection ts = new TestConnection();
    private Connection conn = ts.getConnection();
    private static LaporanController lapKontrol;

    public LaporanController() {
    }

    public LaporanController(Connection konn) {
        this.conn = konn;
    }

    public static LaporanController getConnection() {
        TestConnection connect = new TestConnection();
        if (lapKontrol == null) {
            lapKontrol = new LaporanController(connect.getConnection());
        }
        return lapKontrol;
    }

    public Object[][] getAllBarangBulan(int x, int y) throws SQLException {
        PreparedStatement pt = null;
        ResultSet rs = null;
        String a;
        if (x < 10) {
            a = "0" + x + "-" + y;
        } else {
            a = x + "-" + y;
        }
        String iner2 = "select transaksi.idtransaksi from transaksi where to_char(transaksi.tanggaltransaksi,'mm-yyyy') ='" + a + "'";
        String iner1 = "select sum(jumlahbarang) from detailtransaksi where detailtransaksi.kodebarang = barang.kodebarang and detailtransaksi.idtransaksi in (" + iner2 + ")";
        String sql = "select kodebarang, namabarang,stok, (" + iner1 + ")* hargabarang as total,(select sum(jumlahbarang) from detailtransaksi where detailtransaksi.kodebarang = barang.kodebarang and detailtransaksi.idtransaksi in (" + iner2 + "))as jumlah from barang";

        pt = conn.prepareStatement(sql);
        rs = pt.executeQuery();
        List<Object[]> kategoris = new ArrayList<Object[]>();
        while (rs.next()) {
            Object[] ob = new Object[5];
            ob[0] = rs.getString(1);
            ob[1] = rs.getString(2);
            ob[2] = rs.getInt(3);
            ob[3] = rs.getInt(4);
            ob[4] = rs.getInt(5);

            kategoris.add(ob);
        }

        Object[][] obj = new Object[kategoris.size()][5];
        for (int i = 0; i < kategoris.size(); i++) {
            obj[i] = kategoris.get(i);
        }

        return obj;
    }

    public Object[][] getAllBarangMinggu(java.util.Date x, java.util.Date y) throws SQLException {
        PreparedStatement pt = null;
        ResultSet rs = null;
        String a;
        System.out.println(x);
        System.out.println(y);
        String iner2 = "select transaksi.idtransaksi from transaksi where transaksi.tanggaltransaksi between ? and ? ";
        String iner1 = "select sum(jumlahbarang) from detailtransaksi where detailtransaksi.kodebarang = barang.kodebarang and detailtransaksi.idtransaksi in (" + iner2 + ")";
        String sql = "select kodebarang, namabarang,stok, (" + iner1 + ")* hargabarang as total,(select sum(jumlahbarang) from detailtransaksi where detailtransaksi.kodebarang = barang.kodebarang and detailtransaksi.idtransaksi in (" + iner2 + "))as jumlah from barang";
        System.out.println(sql);
        pt = conn.prepareStatement(sql);
        java.sql.Date dateX = new java.sql.Date(x.getYear(), x.getMonth(), x.getDate());
        java.sql.Date dateY = new java.sql.Date(y.getYear(), y.getMonth(), y.getDate());
        pt.setDate(1, dateX);
        pt.setDate(2, dateY);
        pt.setDate(3, dateX);
        pt.setDate(4, dateY);

        rs = pt.executeQuery();
        List<Object[]> kategoris = new ArrayList<Object[]>();
        while (rs.next()) {
            Object[] ob = new Object[5];
            ob[0] = rs.getString(1);
            ob[1] = rs.getString(2);
            ob[2] = rs.getInt(3);
            ob[3] = rs.getInt(4);
            ob[4] = rs.getInt(5);

            kategoris.add(ob);
        }

        Object[][] obj = new Object[kategoris.size()][5];
        for (int i = 0; i < kategoris.size(); i++) {
            obj[i] = kategoris.get(i);
        }

        return obj;

    }
}
